package modelo.servicio;

import controlador.SeccionControlador;
import java.io.*;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import jcifs.smb.NtlmPasswordAuthentication;
import jcifs.smb.SmbFile;
import modelo.bd.excepcion.DAOException;
import modelo.dominio.Documento;
import modelo.dominio.Historico;
import modelo.dominio.Seccion;

/**
 *
 * @author FloresDA
 */
public class Portable {

    //SINGLETON
    private static Portable instancia;
    private String updatedDbPath;

    //SINGLETON
    private Portable() {
        updatedDbPath = SeccionControlador.getDirectorioTemporal();
    }

    //SINGLETON
    public static Portable instancia() {
        if (instancia == null) {
            instancia = new Portable();
        }
        return instancia;
    }

    //SINGLETON
    @Override
    protected Object clone() throws CloneNotSupportedException {
        return new CloneNotSupportedException("No se puede clonar Portable.");
    }
    private Connection connectToAccess(String dir) {
        Connection connection = null;
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            String dbConnectionString = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=";
            String URL = dbConnectionString + dir;
            connection = DriverManager.getConnection(URL);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return connection;
    }

    private void closeConnecion(Connection conn) {

        try {
            if (conn != null) {
                conn.commit();
                conn.close();
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }

    }

    private void executeQuery(Connection conn, String SQL) {
        Statement stmt = null;
        try {
            stmt = conn.createStatement();
            stmt.executeUpdate(SQL);
            stmt.close();
            conn.commit();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    private void alimentarBase(Connection con, Integer area)
            throws DAOException {
        String pre_hist = "INSERT INTO SSMA_HISTORICO_T"
                + "(ID_HISTORICO, "
                + " USUARIO_CREA, "
                + " FECHA_CREA, "
                + " USUARIO_MOD, "
                + " FECHA_MOD,"
                + " ESTADO) "
                + " VALUES ";
        List<Documento> docs = Documentos.instancia().obtenerPorArea(area);
        String pre = " INSERT INTO SSMA_DOCUMENTOS_T "
                + "(CLAVE_DOC, "
                + " FOLIO, "
                + " RUTA, "
                + " ID_SECCION, "
                + " DESCRIPCION, "
                + " TIPO_DOC, "
                + " LUGAR_FISICO, "
                + " ID_HISTORICO, "
                + " PRECEDENTE) "
                + " VALUES ";
        for (Documento doc : docs) {
            String val = " ('" + doc.getClave() + "'";
            val += ",'" + doc.getFolio() + "'";
            val += ",'" + doc.getRuta() + "'";
            val += "," + doc.getSeccion().getIdSeccion();
            val += ",'" + doc.getDescripcion() + "'";
            val += ",'" + doc.getTipoDoc() + "'";
            val += ",'" + doc.getLugarFisico() + "'";
            val += "," + doc.getHistorico().getIdHistorico();
            val += (doc.getPrecedente() == null ? ", NULL)" : (",'" + doc.getPrecedente().getClave() + "')"));
            executeQuery(con, pre + val);

            val = "(" + doc.getHistorico().getIdHistorico();
            val += ",'" + doc.getHistorico().getUsuarioCrea() + "'";
            val += ",#" + (new SimpleDateFormat("yyyy-MM-dd hh:mm:ss")).format(doc.getHistorico().getFechaCrea()) + "#";
            val += ",'" + doc.getHistorico().getUsuarioMod() + "'";
            val += ",#" + (new SimpleDateFormat("yyyy-MM-dd hh:mm:ss")).format(doc.getHistorico().getFechaMod()) + "#";
            val += ",'" + doc.getHistorico().getEstado() + "')";
            executeQuery(con, pre_hist + val);
        }
        List<Seccion> seccs = Secciones.instancia().obtenerPorArea(area.toString());
        pre = " INSERT INTO SSMA_SECCIONES_T "
                + "(ID_SECCION, NOMBRE, ABREVIACION, ID_AREA, PADRE, ID_HISTORICO)"
                + " VALUES ";
        for (Seccion secc : seccs) {
            String val = "(" + secc.getIdSeccion();
            val += ",'" + secc.getNombre() + "'";
            val += ",'" + secc.getAbreviacion() + "'";
            val += "," + secc.getArea().getIdArea();
            val += "," + (secc.getPadre() == null ? "null" : secc.getPadre().getIdSeccion());
            val += "," + secc.getHistorico().getIdHistorico() + ")";
            executeQuery(con, pre + val);

            val = "(" + secc.getHistorico().getIdHistorico();
            val += ",'" + secc.getHistorico().getUsuarioCrea() + "'";
            (new SimpleDateFormat("yyyy-MM-dd hh:mm:ss")).format(secc.getHistorico().getFechaCrea());
            val += ",#" + (new SimpleDateFormat("yyyy-MM-dd hh:mm:ss")).format(secc.getHistorico().getFechaCrea()) + "#";
            val += ",'" + secc.getHistorico().getUsuarioMod() + "'";
            val += ",#" + (new SimpleDateFormat("yyyy-MM-dd hh:mm:ss")).format(secc.getHistorico().getFechaMod()) + "#";
            val += ",'" + secc.getHistorico().getEstado() + "')";
            executeQuery(con, pre_hist + val);
        }

    }

    private void truncarBase(Connection con)
            throws SQLException {
        con.createStatement().execute("DELETE FROM SSMA_DOCUMENTOS_T");
        con.createStatement().execute("DELETE FROM SSMA_SECCIONES_T");
        con.prepareStatement("DELETE FROM SSMA_HISTORICO_T").execute();
    }

    private ArrayList<Integer> obtenerIdSecciones(Connection con)
            throws SQLException {
        String query = "SELECT ID_SECCION FROM SSMA_SECCIONES_T WHERE ID_SECCION IN "
                + " (SELECT ID_SECCION FROM SSMA_DOCUMENTOS_T)";
        ResultSet rs = con.createStatement().executeQuery(query);
        ArrayList<Integer> secs = new ArrayList<Integer>();
        while (rs.next()) {
            secs.add(rs.getInt("ID_SECCION"));
        }
        return secs;
    }

    private ArrayList<Documento> obtenerDocumentos(Connection con)
            throws SQLException {
        String query = "SELECT * FROM SSMA_DOCUMENTOS_T SDT, SSMA_HISTORICO_T SHT "
                + " WHERE SDT.ID_HISTORICO = SHT.ID_HISTORICO ";
        ResultSet resultado = con.createStatement().executeQuery(query);
        ArrayList<Documento> docs = new ArrayList<Documento>();
        while (resultado.next()) {
            Documento doc = new Documento();
            doc.setClave(resultado.getString("CLAVE_DOC"));
            doc.setFolio(resultado.getString("FOLIO"));
            doc.setTipoDoc(resultado.getString("TIPO_DOC"));
            doc.setRuta(resultado.getString("RUTA"));
            doc.setDescripcion(resultado.getString("DESCRIPCION"));
            doc.setLugarFisico(resultado.getString("LUGAR_FISICO"));
            doc.setSeccion(new Seccion(resultado.getInt("ID_SECCION")));
            if (resultado.getString("PRECEDENTE") != null) {
                doc.setPrecedente(new Documento(resultado.getString("PRECEDENTE")));
            }
            doc.setHistorico(new Historico());
            doc.getHistorico().setEstado(resultado.getString("ESTADO"));
            doc.getHistorico().setUsuarioCrea(resultado.getString("USUARIO_CREA"));
            doc.getHistorico().setUsuarioMod(resultado.getString("USUARIO_MOD"));
            doc.getHistorico().setIdHistorico(resultado.getInt("ID_HISTORICO"));
            doc.getHistorico().setFechaCrea(resultado.getTimestamp("FECHA_CREA"));
            doc.getHistorico().setFechaMod(resultado.getTimestamp("FECHA_MOD"));
            docs.add(doc);
        }
        return docs;
    }

    private void updateRuta(Connection con)
            throws SQLException {
        ArrayList<Documento> docs = obtenerDocumentos(con);
        for (Documento doc : docs) {
            String src = "\\" + doc.getSeccion().getIdSeccion() + "\\"
                    + doc.getRuta().substring(doc.getRuta().lastIndexOf("\\") + 1);
            String query = " UPDATE SSMA_DOCUMENTOS_T SET RUTA = '" + src + "' "
                    + " WHERE CLAVE_DOC = '" + doc.getClave() + "'";
            con.createStatement().execute(query);
        }
    }

    public boolean actualizar(String ip, String drive, Integer area) {
        try {
            drive = drive.replace("/", "\\").replace(":", "$");
            String direccionUsuario = "\\\\" + ip + "\\" + drive
                    + (drive.lastIndexOf("\\") == drive.length() - 1 ? "" : "\\")
                    + "SSYMA\\";

            // Truncar DBnew
            Connection updatedDb = connectToAccess(updatedDbPath + "updatedDB.accdb");
            truncarBase(updatedDb);
            updatedDb.close();
            closeConnecion(updatedDb);

            // Conectar al Cliente (?)

            // Copiar DB_user a DB_server si existe
            InputStream inStream;
            OutputStream outStream;
            File userDbFile = new File(direccionUsuario + "DB.accdb");
            if (!userDbFile.exists()) {
                userDbFile = new File(updatedDbPath + "updatedDB.accdb");
            }
            File serverDbFile = new File(updatedDbPath + "oldDB.accdb");
            inStream = new FileInputStream(userDbFile);
            outStream = new FileOutputStream(serverDbFile);
            byte[] buffer = new byte[1024];
            int length;
            //copy the file content in bytes 
            while ((length = inStream.read(buffer)) > 0) {
                outStream.write(buffer, 0, length);
            }
            inStream.close();
            outStream.close();

            // Alimentar DBnew
            updatedDb = connectToAccess(updatedDbPath + "updatedDB.accdb");
            alimentarBase(updatedDb, area);

            // Comparar seccsEnviar = DBnew - DBold
            Connection oldDb = connectToAccess(updatedDbPath + "oldDB.accdb");
            ArrayList<Integer> oldSecs = obtenerIdSecciones(oldDb);
            ArrayList<Integer> updatedSecs = obtenerIdSecciones(updatedDb);
            updatedSecs.removeAll(oldSecs);

            // Crear seccsEnviar
            for (Integer sec : updatedSecs) {
                File secDir = new File(direccionUsuario + sec + "\\");
                secDir.mkdirs();
            }

            // Comparar docsEnviar = DBnew - DBold
            ArrayList<Documento> oldDocs = obtenerDocumentos(oldDb);
            ArrayList<Documento> updatedDocs = obtenerDocumentos(updatedDb);
            updatedDocs.removeAll(oldDocs);

            // Crear docsEnviar
            for (Documento doc : updatedDocs) {
                String docUsuarioSrc = direccionUsuario + doc.getSeccion().getIdSeccion() + "\\"
                        + doc.getRuta().substring(doc.getRuta().lastIndexOf("\\") + 1);
                File docUsuario = new File(docUsuarioSrc);
                outStream = new FileOutputStream(docUsuario);

                //----------CIFS
                NtlmPasswordAuthentication auth = new NtlmPasswordAuthentication(SeccionControlador.getUsuarioCIFS());
                String path = "smb:" + doc.getRuta().replace("\\", "/");
                SmbFile sFile = new SmbFile(path, auth);
                inStream = sFile.getInputStream();
                //----------Normal
//                File docServidor = new File(doc.getRuta());
//                inStream = new FileInputStream(docServidor);
                //----------

                buffer = new byte[1024];
                while ((length = inStream.read(buffer)) > 0) {
                    outStream.write(buffer, 0, length);
                }
                inStream.close();
                outStream.close();

            }
            updateRuta(updatedDb);
            // Cerrar DBnew, DBold
            closeConnecion(updatedDb);
            closeConnecion(oldDb);

            // Enviar DBnew --> DB
            userDbFile = new File(direccionUsuario + "DB.accdb");
            serverDbFile = new File(updatedDbPath + "updatedDB.accdb");
            inStream = new FileInputStream(serverDbFile);
            outStream = new FileOutputStream(userDbFile);
            buffer = new byte[1024];
            //copy the file content in bytes 
            while ((length = inStream.read(buffer)) > 0) {
                outStream.write(buffer, 0, length);
            }
            inStream.close();
            outStream.close();

            // Borrar DBold
            File oldDbFile = new File(updatedDbPath + "oldDB.accdb");
            if (oldDbFile.exists()) {
                oldDbFile.delete();
            }
            return true;
        } catch (Exception ex) {
            return false;
        }
    }
}
